package com.ph.irp.base.bom.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.bom.dao.BOMBillDetailDAO;
import com.ph.irp.dto.BOMBillDetailDTO;
import com.ph.irp.model.PBaseBombill;
import com.ph.irp.model.PBaseBombilldetail;
import com.ph.util.DBUtil;

public class BOMBillDetailDAOImpl implements BOMBillDetailDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void addBOMBillDetail(PBaseBombilldetail bomBillDatail)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" insert into p_base_bombilldetail(BOMBillID,MaterialID,");
		sb.append("AssistantAttributeID,StorageID,UsedNumber,LossPercent,MeasureUnitID)");
		sb.append(" values(");
		sb.append(bomBillDatail.getPBaseBombillId());
		sb.append(",").append(bomBillDatail.getPBaseVerifyprojectMaterialId());
		sb.append(",").append(
				bomBillDatail.getPBaseMaterialAssistantattributeId());
		sb.append(",").append(bomBillDatail.getPBaseVerifyprojectStorageId());
		sb.append(",").append(bomBillDatail.getUsedNumber());
		sb.append(",").append(bomBillDatail.getLossPercent());
		sb.append(",").append(bomBillDatail.getPBaseMeasureUnitId());
		sb.append(")");
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	public List<PBaseBombilldetail> getBOMBillDetailListByBOMBillID(
			String bomBillID) throws Throwable {
		List<PBaseBombilldetail> resList = new ArrayList<PBaseBombilldetail>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb
				.append("select b.BOMBillDetailID bomBillDetailID,b.BOMBillID bomBillID,");
		sb
				.append("b.MaterialID metailID,b.AssistantAttributeID assistantAttributeID,");
		sb.append("b.StorageID storageID,b.UsedNumber usedNumber,");
		sb.append("b.LossPercent lossPercent,b.MeasureUnitID measureUnitID ");
		sb.append("from p_base_bombilldetail b ");
		sb.append("where BOMBillID =").append(Integer.parseInt(bomBillID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseBombilldetail bomBillDetail = new PBaseBombilldetail();
				bomBillDetail.setBombillDetailId(rs.getInt("bomBillDetailID"));
				bomBillDetail.setPBaseBombillId(rs.getInt("bomBillID"));
				bomBillDetail.setPBaseVerifyprojectMaterialId(rs
						.getInt("metailID"));
				bomBillDetail.setPBaseMaterialAssistantattributeId(rs
						.getInt("assistantAttributeID"));
				bomBillDetail.setPBaseVerifyprojectStorageId(rs
						.getInt("storageID"));
				bomBillDetail.setUsedNumber(rs.getDouble("usedNumber"));
				bomBillDetail.setLossPercent(rs.getInt("lossPercent"));
				bomBillDetail.setPBaseMeasureUnitId(rs.getInt("measureUnitID"));
				resList.add(bomBillDetail);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

		return resList;

	}

	public void deleteBOMBillDetailByBOMBillID(String bomBillID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from p_base_bombilldetail ");
		sb.append(" where BOMBillID =").append(bomBillID);
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}
}
